IF OBJECT_ID('dbo.GetDeliverableTypes') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.GetDeliverableTypes
    IF OBJECT_ID('dbo.GetDeliverableTypes') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.GetDeliverableTypes >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.GetDeliverableTypes >>>'
END
go
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER OFF
go
-- =======================================================================
-- Procedure Name: dbo.GetDeliverableTypes
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 11/12/2008
--
-- Description: Returns list of Deliverable Types for Subcontract Reports
--
-- Parameters:  None
--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.GetDeliverableTypes()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 11/12/08    KC        Created
-- 12/01/08    KC        Added two new REPORTCATs
-- 12/02/08    KC        ...and Two MORE...(left placeholder for IN & OUT
--                            groups...
-- 12/11/08    KC        Added Description
-- 12/18/08    KC        Added New to Descriptions.
-- 12/26/08    KC        Changed All-SubIn to Only-Subin, All-SubOut to Only-SubOut
-- 12/29/08    KC        Changed DeliverableType param values, for display in Rpt.
--
-- =======================================================================
CREATE PROCEDURE dbo.GetDeliverableTypes
AS
BEGIN
     /*  Returned Fields for Dropdown
     --------------------------------
     DELTYPE             VARCHAR             
     DELTYPEID           VARCHAR
     */

     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error            INT,
               @RowCount         INT

     -- Result Query    
     SELECT 'ALL'   AS DELTYPE,
               0    AS DELTYPEID,
            'All (New SubsIn/Out, PSA & MODs)' AS DELDESCR
     UNION
     SELECT 'AllSubsIn',
               1,
            'All Subs In (New SubsIn, PSA & MODs)'
     UNION
     SELECT 'AllSubsOut',
               2,
            'All Subs Out (New SubsOut, PSA & MODs)'
     UNION
     SELECT 'Only-SubIn',
               3,
            'Only SubsIn'
     UNION
     SELECT 'Only-SubOut',
               4,
            'Only SubsOut'
     UNION
     SELECT 'SubIn-MOD',
               5,
            'Only MODS In'
     UNION
     SELECT 'SubOut-MOD',
               6,
            'Only MODS Out'
     UNION
     SELECT 'PSA-IN',
               7,
            'Only PSA-In'
     UNION
     SELECT 'PSA-OUT',
               8,
            'Only PSA-Out'
     ORDER BY DELTYPEID              
     
     
     
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1

     -- Return Success
     RETURN 0
     
END
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
IF OBJECT_ID('dbo.GetDeliverableTypes') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.GetDeliverableTypes >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.GetDeliverableTypes >>>'
go
GRANT EXECUTE ON dbo.GetDeliverableTypes TO insight
go
GRANT EXECUTE ON dbo.GetDeliverableTypes TO InsightReport
go
